package DBTasks;

import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;

public class CurrentQuarterlyReport extends DBTask {
    private static String calculateCurrentQuarterQuery = "SELECT QUARTER(NOW())";
    private static String createEventsBySheltersView = "CREATE OR REPLACE VIEW EventsByShelter AS SELECT Sponsor_Name as Shelter_Name, MONTH(Date) Month, COUNT(*) as EventCount FROM Event GROUP BY Sponsor_Name, Month";
    private static String createDonationsByShelterView = "CREATE OR REPLACE VIEW DonationsByShelter AS SELECT Shelter_Name as sn1, MONTH(Date) as m1, SUM(Amount) as DonationSum FROM Donation GROUP BY Shelter_Name, m1";
    private static String createAdoptionsByShelterView = "CREATE OR REPLACE VIEW AdoptionsByShelter AS SELECT Shelter_Name as sn2, MONTH(Date) m2, COUNT(*) as AdoptionCount FROM Adopts GROUP BY Shelter_Name, m2";
    private static String createPetsReceivedByShelterView = "CREATE OR REPLACE VIEW PetsReceivedByShelter AS SELECT Shelter_Name as sn3, MONTH(Date_Assigned) m3, COUNT(*) as PetsReceivedCount FROM Pet GROUP BY Shelter_Name, m3";
    private static String createSummaryView = "CREATE OR REPLACE VIEW Summary AS SELECT * FROM EventsByShelter ebs "
            + "LEFT JOIN DonationsByShelter dbs ON ebs.Shelter_Name=dbs.sn1 AND ebs.Month=dbs.m1 " + "LEFT JOIN AdoptionsByShelter abs ON ebs.Shelter_Name=abs.sn2 AND ebs.month=abs.m2 "
            + "LEFT JOIN PetsReceivedByShelter prbs ON ebs.Shelter_Name=prbs.sn3 AND ebs.month=prbs.m3";

    private static String quarterSummaryQuery = "SELECT Shelter_Name, Month, EventCount, DonationSum, AdoptionCount, PetsReceivedCount FROM QuarterSummary WHERE Month=? OR Month=? OR Month=?";

    public static ArrayList<Summary> generateCurrentQuarterlyReport() {

        ArrayList<Summary> currentQuarterSummaries = new ArrayList<Summary>();
        PreparedStatement quarterSummaryStatement = prepareQuarterSummaryQuery(calculateCurrentQuarter());
        createViews();
        
        try {
            quarterSummaryStatement.execute();
            ResultSet rs = quarterSummaryStatement.getResultSet();
            while(rs.next()){
                String name = rs.getString("Shelter_Name");
                int month = rs.getInt("Month");
                int eventCount = rs.getInt("EventCount");
                int donationSum = rs.getInt("DonationSum");
                int adoptionCount = rs.getInt("AdoptionCount");
                int petsReceivedCount = rs.getInt("PetsReceivedCount");
                Summary toInsert = new Summary(name, month, eventCount, donationSum, adoptionCount, petsReceivedCount);
                currentQuarterSummaries.add(toInsert);
            }
            
            return currentQuarterSummaries;
        }
        catch (SQLException e) {
            throw new RuntimeException(e);
        }
    }

    private static void createViews(){
        try {
            Statement executor = con.createStatement();
            executor.executeUpdate(createEventsBySheltersView);
            executor.executeUpdate(createDonationsByShelterView);
            executor.executeUpdate(createAdoptionsByShelterView);
            executor.executeUpdate(createPetsReceivedByShelterView);
            executor.executeUpdate(createSummaryView);
        }
        catch (SQLException e) {
            throw new RuntimeException(e);
        }
        
    }
    
    private static int calculateCurrentQuarter() {
        int currentQuarter = 0;
        Statement executor;
        try {
            executor = con.createStatement();
            executor.execute(calculateCurrentQuarterQuery);
            ResultSet currentQuarterResults = executor.getResultSet();
            currentQuarterResults.first();
            currentQuarter = currentQuarterResults.getInt(1);
        }
        catch (SQLException e) {
            throw new RuntimeException(e);
        }

        return currentQuarter;
    }
    
    public static String[] calculateMonths() {
    	
    	String[] months = new String[3];
    	int quarter = calculateCurrentQuarter();
    	
    	switch (quarter) {
        case 1:
            months[0] = "January";
            months[1] = "February";
            months[2] = "March";
            break;
        case 2:
        	months[0] = "April";
            months[1] = "May";
            months[2] = "June";
            break;
        case 3:
        	months[0] = "July";
            months[1] = "August";
            months[2] = "September";
            break;
        case 4:
        	months[0] = "October";
            months[1] = "November";
            months[2] = "December";
            break;
        }
    	
    	return months;
    	
    }

    private static PreparedStatement prepareQuarterSummaryQuery(int currentQuarter) {
        try {
            PreparedStatement ps = con.prepareStatement(quarterSummaryQuery);
            switch (currentQuarter) {
            case 1:
                ps.setInt(1, 1);
                ps.setInt(2, 2);
                ps.setInt(3, 3);
                break;
            case 2:
                ps.setInt(1, 4);
                ps.setInt(2, 5);
                ps.setInt(3, 6);
                break;
            case 3:
                ps.setInt(1, 7);
                ps.setInt(2, 8);
                ps.setInt(3, 9);
                break;
            case 4:
                ps.setInt(1, 10);
                ps.setInt(2, 11);
                ps.setInt(3, 12);
                break;
            }
            return ps;
        }
        catch (SQLException e) {
            throw new RuntimeException(e);
        }
    }

}
